﻿using System;
using System.Collections.Generic;
using System.Linq;
using NHibernate;
using NHibernate.Criterion;
using OurSite.Business.Category;

namespace OurSite.Business.Repositories
{
    public class CategoryRepository : BaseRepository, ICategoryRepository
    {
        public void Add(Category.Category category)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Save(category);
                transaction.Commit();
            }
        }

        public void AddEventCategory(Guid eventId, Guid categoryId)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                Object result = session.CreateSQLQuery("INSERT INTO EventCategory (Event_ID, Category_ID) VALUES (:event_id, :category_id)")
                    .SetParameter("event_id", eventId)
                    .SetParameter("category_id", categoryId)
                    .UniqueResult();

                transaction.Commit();
            }
        }

        public void DeleteEventCategory(Guid eventId, Guid categoryId)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                Object result = session.CreateSQLQuery("DELETE FROM EventCategory WHERE Event_ID = :event_id AND Category_ID = :category_id")
                    .SetParameter("event_id", eventId)
                    .SetParameter("category_id", categoryId)
                    .UniqueResult();

                transaction.Commit();
            }
        }

        public Category.Category GetById(Guid id)
        {
            using (var tx = session.BeginTransaction())
            {
                var category = session.Get<Category.Category>(id);
                tx.Commit();
                return category;

            }
        }

        public Category.Category GetByName(string name)
        {
            using (var tx = session.BeginTransaction())
            {
                Category.Category category = session
                    .CreateCriteria(typeof (Category.Category))
                    .Add(Restrictions.Eq("Name", name))
                    .UniqueResult<Category.Category>();

                tx.Commit();
                return category;
            }
        }

        public IList<Category.Category> GetByEventId(Guid id)
        {
            using (var tx = session.BeginTransaction())
            {
                var evt = session.Get<Event.Event>(id);
                tx.Commit();
                return evt.EventCategories;
            }
        }

        public IList<Category.Category> GetAllParentCategories()
        {
            using (var tx = session.BeginTransaction())
            {
                var categories = session
                    .CreateCriteria<Category.Category>()
                    .Add(Expression.Or
                             (
                             Restrictions.IsNull(Projections.Property("ParentId")),
                             Restrictions.Eq(Projections.Property("ParentId"), Guid.Empty)
                             ))
                    .AddOrder(Order.Asc("Name"))
                    .List<Category.Category>();

                tx.Commit();
                return categories;
            }
        }

        public IList<Category.Category> GetSubCategories(Guid parentId)
        {
            using (var tx = session.BeginTransaction())
            {
                var categories = session
                    .CreateCriteria<Category.Category>()
                    .Add(Restrictions.Eq(Projections.Property("ParentId"), parentId))
                    .AddOrder(Order.Asc("Name"))
                    .List<Category.Category>();

                tx.Commit();
                return categories;
            }
        }

        public List<CategoryLight> GetAllCategories()
        {

            IList<Category.Category> cats = GetAllParentCategories();

            var catl = (from cat in cats select new CategoryLight(cat)).ToList();

            return catl;
        }

    }
}
